package servlet.dao;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import servlet.bean.UserBean;
import servlet.dbconnect.DBDriverFactory;

public class UserInfoManagerDao {
	
	public int saveUserInfo(UserBean userB) {
		
		int re_i = 0;
		if(userB.getUser_id()==null){
			//新增处理
			String strSql = "INSERT INTO TBUSER(USERNAME ,USER_SEX ,USER_AGE,USER_PSW ,USER_PHONE ,USER_ADDRESS)VALUES(?,?,?,?,?,?)";
			re_i = DBDriverFactory.getDBDriverInstance().update(strSql,new Object[]{userB.getUsername(),userB.getUser_sex(),userB.getUser_age(),userB.getUser_psw(),userB.getUser_phone(),userB.getUser_address()});
		}else{
			//修改处理
			String strsql = "UPDATE TBUSER SET USERNAME=? , USER_SEX=? , USER_AGE=? , USER_PSW=? , USER_PHONE=? , USER_ADDRESS=? WHERE USER_ID= ?";
			re_i = DBDriverFactory.getDBDriverInstance().update(strsql,new Object[]{userB.getUsername(),userB.getUser_sex(),userB.getUser_age(),userB.getUser_psw(),userB.getUser_phone(),userB.getUser_address(),userB.getUser_id()});
		}
		return re_i;
	}
	
	public List<UserBean> queryUserInfo(UserBean userB){
		StringBuilder sbSql= new StringBuilder("SELECT * FROM TBUSER WHERE 1=1 ");
		List<Object> lstParas= new ArrayList<Object>();
		if(userB.getUsername()!=null&& !userB.getUsername().trim().equals("")){
			sbSql.append("AND USERNAME LIKE ? ");
			lstParas.add("%%"+userB.getUsername()+"%%");
		}
		if(userB.getUser_sex()!=null&& !userB.getUser_sex().trim().equals("")){
			sbSql.append("AND USER_SEX = ?");
			lstParas.add(userB.getUser_sex());
		}
		if(userB.getUser_age()>0){
			sbSql.append("AND USER_AGE >= ? ");
			lstParas.add(userB.getUser_age());
		}
		if(userB.getUser_age2()>0){
			sbSql.append("AND USER_AGE <= ? ");
			lstParas.add(userB.getUser_age2());
		}
		if(userB.getUser_address()!=null&& !userB.getUser_address().trim().equals("")){
			sbSql.append("AND USER_ADDRESS LIKE ? ");
			lstParas.add("%%"+userB.getUser_address()+"%%");
		}
		if(userB.getUser_phone()!=null&& !userB.getUser_phone().trim().equals("")){
			sbSql.append("AND USER_PHONE LIKE ? ");
			lstParas.add("%%"+userB.getUser_phone()+"%%");
		}
		sbSql.append(" ORDER BY USER_ID ");
		
		List<UserBean> lstUser = DBDriverFactory.getDBDriverInstance().query(sbSql.toString(),new BeanListHandler<UserBean>(UserBean.class), lstParas.toArray());
		return lstUser;
	}
	
	public int deleteUserById(int userId) {
		int re_i = 0;
		// 执行的sql语句
		String strSql = "DELETE FROM TBUSER WHERE USER_ID=?";
		// 删除数据
		re_i = DBDriverFactory.getDBDriverInstance().update(strSql, new Object[] { userId });
		return re_i;
	}
	
	
	public List<UserBean> getAllUserInfo(){
		String strSql="SELECT * FROM TBUSER ORDER BY USER_ID DESC";
		List<UserBean> lstUser = DBDriverFactory.getDBDriverInstance().query(strSql,new BeanListHandler<UserBean>(UserBean.class));
		return lstUser;
	}
	
	public UserBean getUserInfoById(String strUid) {
		// 查询数据库的sql语句
        String strSql = "SELECT * FROM TBUSER WHERE USER_ID=?";
		UserBean UserB = DBDriverFactory.getDBDriverInstance().query(strSql,
				new BeanHandler<UserBean>(UserBean.class), new Object[] { strUid });
		return UserB;
	}
}
